"""
添加推荐系统相关表的数据库迁移脚本
"""

from sqlalchemy import MetaData, Table, Column, String, Text, DateTime, Float, JSON, ForeignKey
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime
import uuid

# 定义新表结构
def upgrade(migrate_engine):
    """创建新的推荐系统相关表"""
    meta = MetaData()
    meta.reflect(bind=migrate_engine)

    # 用户兴趣标签表
    user_interests_table = Table(
        'user_interests',
        meta,
        Column('id', UUID(as_uuid=True), primary_key=True, default=uuid.uuid4),
        Column('user_id', UUID(as_uuid=True), ForeignKey('users.id'), nullable=False, index=True),
        Column('interest_tag', String(100), nullable=False, index=True),
        Column('weight', Float, default=1.0),  # 兴趣强度
        Column('category', String(50)),  # 兴趣分类
        Column('last_interacted', DateTime, default=datetime.utcnow),  # 最后交互时间
        Column('created_at', DateTime, default=datetime.utcnow, nullable=False),
        Column('updated_at', DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)
    )

    # 内容项目表
    content_items_table = Table(
        'content_items',
        meta,
        Column('id', UUID(as_uuid=True), primary_key=True, default=uuid.uuid4),
        Column('title', String(200), nullable=False),
        Column('description', Text),
        Column('content_type', String(50), nullable=False),  # 类型: 文档、视频、问答等
        Column('tags', JSON, nullable=True),  # 内容标签
        Column('category', String(50)),  # 内容分类
        Column('metadata', JSON, nullable=True),  # 其他元数据
        Column('embedding', JSON, nullable=True),  # 内容向量表示
        Column('created_at', DateTime, default=datetime.utcnow, nullable=False),
        Column('updated_at', DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)
    )

    # 用户内容交互记录表
    user_content_interactions_table = Table(
        'user_content_interactions',
        meta,
        Column('id', UUID(as_uuid=True), primary_key=True, default=uuid.uuid4),
        Column('user_id', UUID(as_uuid=True), ForeignKey('users.id'), nullable=False, index=True),
        Column('content_id', UUID(as_uuid=True), ForeignKey('content_items.id'), nullable=False, index=True),
        Column('interaction_type', String(50), nullable=False),  # 查看、点击、收藏、评分等
        Column('score', Float, default=0.0),  # 交互评分
        Column('metadata', JSON, nullable=True),  # 交互元数据
        Column('created_at', DateTime, default=datetime.utcnow, nullable=False, index=True)
    )

    # 创建表
    user_interests_table.create(migrate_engine)
    content_items_table.create(migrate_engine)
    user_content_interactions_table.create(migrate_engine)


def downgrade(migrate_engine):
    """删除推荐系统相关表"""
    meta = MetaData()
    meta.reflect(bind=migrate_engine)

    # 删除表
    user_content_interactions_table = Table('user_content_interactions', meta, autoload_with=migrate_engine)
    content_items_table = Table('content_items', meta, autoload_with=migrate_engine)
    user_interests_table = Table('user_interests', meta, autoload_with=migrate_engine)

    user_content_interactions_table.drop(migrate_engine)
    content_items_table.drop(migrate_engine)
    user_interests_table.drop(migrate_engine)